package db.accountbook;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.Database;
import dbservice.accountbook.AccountBookDBService;
import po.AccountBookPo;
import po.Po;

public class AccountBookDB implements AccountBookDBService{
	private static final String creatTable = "CREATE TABLE IF NOT EXISTS `seac2`.`accountbook` (  `name` VARCHAR(45) NOT NULL,  `info` MEDIUMBLOB,  `dfault` VARCHAR(45),PRIMARY KEY (`name`),`date` BIGINT(0) NOT NULL,  UNIQUE INDEX `name_UNIQUE` (`name` ASC));";
	private static final String creatDefaultAB="insert into accountbook values('default',null,'true',"+new Date().getTime()+");";
	public AccountBookDB() throws SQLException {
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(creatTable);
		ResultSet r=s.executeQuery("select * from accountbook where name='default';");
		if(!r.next()){
			s.execute(creatDefaultAB);
		}
		s.close();
	}

	@Override
	public AccountBookPo getDefaultAccountBook() {
		AccountBookPo result=new AccountBookPo();
		try {
			Connection c = Database.getConnection();
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select * from accountbook where dfault='true';");
			if(r.next()){
				result.setName(r.getString(1));
				result.setInfo(blob2Info(r.getBlob(2)));
				result.setCreatTime(r.getLong(4));
				result.successed();
			}else{
				result.failed("找不到默认账本");
			}
			s.close();
		} catch (SQLException e) {
			result.failed(e.getMessage());
			e.printStackTrace();
		}
		return result;
	}
	
	@Override
	public Po add(AccountBookPo account) {
		Connection c = Database.getConnection();
		Po result=new Po();
		try {			
			c.setAutoCommit(false);
			Statement checkExistence=c.createStatement();
			ResultSet existence=checkExistence.executeQuery("select * from accountbook where name='"+account.getName()+"';");
			if(!existence.next()){
			PreparedStatement s = c.prepareStatement("insert into accountbook values(?,?,'true',?);");
			s.addBatch("update accountbook set dfault=null where dfault='true';");
			s.setString(1, account.getName());
			s.setBlob(2, info2Blob(account.getInfo()));
			s.setLong(3,new Date().getTime());
			s.addBatch();			
			s.executeBatch();
			c.commit();
			result.successed();
			}else {
				result.failed("账户已存在");
			}
			checkExistence.close();
		} catch (SQLException e) {
			try {
				c.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			result.failed(e.getMessage());
			e.printStackTrace();
		} finally{
			try {
				c.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	@Override
	public AccountBookPo get(AccountBookPo account) {
		AccountBookPo result=new AccountBookPo();
		try {
			Connection c = Database.getConnection();
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select * from accountbook where name='"+account.getName()+"';");
			if(r.next()){
				result.setName(r.getString(1));
				result.setInfo(blob2Info(r.getBlob(2)));
				result.setCreatTime(r.getLong(4));
				result.successed();
			}else{
				result.failed("找不到账本");
			}
			s.close();
		} catch (SQLException e) {
			result.failed(e.getMessage());
			e.printStackTrace();
		}
		return result;
	}
	
	@Override
	public List<AccountBookPo> getAll() {
		List<AccountBookPo> result=new ArrayList<AccountBookPo>();
		AccountBookPo temp;
		try {
			
			Connection c = Database.getConnection();
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select * from accountbook;");
			while(r.next()){
				temp=new AccountBookPo();
				temp.setName(r.getString(1));
				temp.setInfo(blob2Info(r.getBlob(2)));
				temp.setCreatTime(r.getLong(4));
				temp.successed();
				result.add(temp);
			}
			s.close();
		} catch (SQLException e) {
			result.clear();
			e.printStackTrace();
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	private List<Object> blob2Info(Blob blob){
		List<Object> result=null;
		if(blob==null){
			return null;
		}
		try {
			ObjectInputStream ois = new ObjectInputStream(blob.getBinaryStream());
			Object object=ois.readObject();
			ois.close();
			if(object instanceof List<?>){
				result=(List<Object>)object;
			}else{
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}		
		return result;
	}
	
	private InputStream info2Blob(List<Object> info){
		ByteArrayOutputStream out=new ByteArrayOutputStream();
		ObjectOutputStream oos;
		InputStream in=null;
		try {
			oos = new ObjectOutputStream(out);
		oos.writeObject(info);
		oos.close();
		in=new ByteArrayInputStream(out.toByteArray());
		
		} catch (IOException e) {
			e.printStackTrace();
		}
		return in;
	}

	
}
